﻿Imports System.IO
Imports System.Text
Imports Mysoft.Map.Data
Imports Mysoft.Map.Application
Imports Mysoft.Map.Excel
Imports System.Data

Partial Public Class KhFx_Tz_ExportExcel
    Inherits System.Web.UI.Page

#Region " Web 窗体设计器生成的代码 "

    '该调用是 Web 窗体设计器所必需的。
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    '注意: 以下占位符声明是 Web 窗体设计器所必需的。
    '不要删除或移动它。
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: 此方法调用是 Web 窗体设计器所必需的
        '不要使用代码编辑器修改它。
        InitializeComponent()
    End Sub

#End Region

#Region " 页面公用函数 "

    '导出Excel
    Private Sub ExportExcel(ByVal strtype As String, ByVal strSchemeGUID As String)
        Dim strExcelFile As String

        '临时文件
        strExcelFile = Server.MapPath(Now.ToString("yyyyMMddHHmmssffff") & ".xls")

        Dim strSQL As String
        Dim DTTemp As DataTable
        Dim i, j, intLine As Integer

        strSQL = "SELECT SchemeName,isnull(Layout,'一维') as Layout,RowFeildList,ColFeildList,FeildTypeList,FeildNameList,FilterStr,SchemeSQL FROM p_CstAnalyseScheme WHERE SchemeGUID='" & strSchemeGUID & "'"
        Try
            DTTemp = MyDB.GetDataTable(strSQL)
        Catch ex As Exception
            ResponseErr1("数据库错误，请稍后重试！", "")
            Exit Sub
        End Try
        If DTTemp.Rows.Count > 0 Then
            Dim strExportName As String
            Dim strRowFeildList As String
            Dim strColFeildList As String
            Dim strFeildNameList As String
            Dim wb As ExcelWookbook = New ExcelWookbook
            Dim ws As ExcelWorksheet
            Dim cell As ExcelCell
            strExportName = DTTemp.Rows(0).Item("SchemeName").ToString
            strRowFeildList = DTTemp.Rows(0).Item("RowFeildList").ToString
            strColFeildList = DTTemp.Rows(0).Item("ColFeildList").ToString
            strFeildNameList = DTTemp.Rows(0).Item("FeildNameList").ToString
            If Session("SchemeSql") Is Nothing Then
                ResponseErr1("系统超时！", "")
                Exit Sub
            End If


            '一维、二维处理
            If strtype = "1" Then
                Dim strLayout As String
                strLayout = Session("SchemeSql").ToString.Substring(0, 1)
                If strLayout = "1" Then '一维
                    strSQL = Session("SchemeSql").ToString.Substring(1)
                Else '二维
                    strSQL = Session("SchemeSql").ToString.Substring(1)
                    strSQL = strSQL.Substring(strSQL.IndexOf(" from vp_CstAnalyseAttribute "))
                    strSQL = Left(strSQL, Len(strSQL) - 22)
                End If


                If strColFeildList <> "" Then
                    strSQL = "  Select " & strRowFeildList & "," & strColFeildList & strSQL
                Else
                    strSQL = "  Select " & strRowFeildList & strSQL
                End If
            Else '多维处理
                strSQL = Session("SchemeSql")
            End If
            Try
                DTTemp = MyDB.GetDataTable(strSQL)
            Catch ex As Exception
                ResponseErr1("数据库错误，请稍后重试！", "")
                Exit Sub
            End Try


            ws = wb.Worksheets.Add("Sheet1")

            Dim strArr() As String = strFeildNameList.Split(",")
            Dim intMax As Integer = strArr.Length

            ws.Rows(0).Height = 240                         '设置行高
            ws.Rows(1).Height = 240
            ws.Rows(2).Height = 240
            For i = 0 To intMax - 1
                ws.Columns(i).Style.Font.Size = 200         '按列设置字体大小

                cell = ws.Cells(0, i)
                cell.Value = strArr(i)
            Next

            For i = 0 To DTTemp.Rows.Count - 1
                intLine = 1 + i                     '定位行
                ws.Rows(intLine).Height = 240

                '问题
                For j = 0 To intMax - 1
                    cell = ws.Cells(intLine, j)
                    cell.Value = DTTemp.Rows(i).Item(j)
                Next
            Next

            Try
                wb.SaveXls(strExcelFile)
            Catch ex As Exception
                ResponseErr1("创建Excel文件时发生异常: " & ex.Message, "")
            End Try

            '输出文件
            ExportFile(strExcelFile, "客户分析" & Now.ToString("yyyyMMddHHmmssffff") & ".xls", True)

        Else
            ResponseErr1("当前方案定义可能已经被删除！", "")
            Exit Sub
        End If
    End Sub

    '提供下载
    Private Sub ExportFile(ByVal strFile As String, ByVal strExportFile As String, Optional ByVal isDel As Boolean = False)
        Dim fs As FileStream
        Dim buffer() As Byte

        fs = New FileStream(strFile, IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.ReadWrite)

        ReDim buffer(fs.Length - 1)
        fs.Read(buffer, 0, fs.Length)
        fs.Close()

        If isDel Then File.Delete(strFile)

        Response.Clear()
        Response.ClearHeaders()
        Response.Buffer = False
        Response.Charset = "gb2312"
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312")       '设置输出流为简体中文 
        Response.ContentType = "application/octet-stream"
        Response.AppendHeader("Content-Disposition", "inline;filename=" & System.Web.HttpUtility.UrlEncode(strExportFile, System.Text.Encoding.UTF8))
        Response.AddHeader("Content-Length", buffer.Length.ToString())
        Me.EnableViewState = False
        Response.BinaryWrite(buffer)

        Response.End()


    End Sub


    Private Sub ResponseErr1(ByVal s As String, ByVal table As String)
        Response.Write("<script language=""javascript"">alert(""" & s.Replace("""", "\""") & """);window.returnValue='" & table & "';window.close();</script>")
    End Sub
#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        '导出Excel
        Dim strtype As String = Request.QueryString("type").ToString
        Dim strSchemeGUID As String = Request.QueryString("oid")

        ExportExcel(strtype, strSchemeGUID)
    End Sub

End Class